package stores;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import workspace.Workspace;
import beans.Foglalo;

public class FoglaloStoreImpl implements FoglaloStore {

	@Override
	public List<Foglalo> loadFoglaloLista(Workspace workspace)
			throws SQLException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Foglalo> foglaloLista = new LinkedList<Foglalo>();
		Foglalo foglalo = null;

		try {
			ps = workspace
					.getPreparedStatement("SELECT ID, ROWVERSION, FNEV, KULSO_BELSO, KTSG_TERMEKID, KTSGVISELO FROM FOGLALO WHERE AKTIV = 1");
			rs = ps.executeQuery();
			while (rs.next()) {
				foglalo = buildFoglalo(rs);
				foglaloLista.add(foglalo);
			}
			return foglaloLista;

		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null) {
				rs.close();
			}
		}

	}

	@Override
	public Foglalo loadFoglaloByName(Workspace workspace, String foglaloNev)
			throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Foglalo foglalo = null;

		try {
			ps = workspace
					.getPreparedStatement("SELECT ID, ROWVERSION, FNEV, KULSO_BELSO, KTSG_TERMEKID, KTSGVISELO FROM FOGLALO WHERE AKTIV = 1 AND MEGNEVEZES = ?");
			int i = 1;
			ps.setString(i, foglaloNev);
			rs = ps.executeQuery();
			while (rs.next()) {
				foglalo = buildFoglalo(rs);
			}
			return foglalo;

		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null) {
				rs.close();
			}
		}
	}

	@Override
	public Foglalo loadFoglaloById(Workspace workspace, long id)
			throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Foglalo foglalo = null;

		try {
			ps = workspace
					.getPreparedStatement("SELECT ID, ROWVERSION, FNEV, KULSO_BELSO, KTSG_TERMEKID, KTSGVISELO FROM FOGLALO WHERE AKTIV = 1 AND ID = ?");
			int i = 1;
			ps.setLong(i++, id);
			rs = ps.executeQuery();
			while (rs.next()) {
				foglalo = buildFoglalo(rs);
			}
			return foglalo;

		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null) {
				rs.close();
			}
		}
	}

	@Override
	public Foglalo insertFoglalo(Workspace workspace, Foglalo ujFoglalo)
			throws SQLException {
		PreparedStatement ps = null;
		workspace.startSession();

		try {
			ps = workspace
					.getPreparedStatement("INSERT INTO FOGLALO(WRITER, FNEV, KULSO_BELSO, KTSG_TERMEKID, KTSGVISELO, AKTIV) VALUES (?, ?, ?, ?, ?, 1)");
			int i = 1;
			ps.setLong(i++, workspace.getAktualisOperator().getId());
			ps.setString(i++, ujFoglalo.getFoglaloNev());
			ps.setBoolean(i++, ujFoglalo.getBelsoE());
			ps.setString(i++, ujFoglalo.getKtsgHely());
			ps.setString(i++, ujFoglalo.getKtsgViselo());
			int sorValtNum = ps.executeUpdate();
			if (sorValtNum == 1) {
				workspace.commit();
				ujFoglalo.setId(getGeneratedKey(workspace));
				ujFoglalo.setRowVersion(1);
				return ujFoglalo;
			} else {
				return null;
			}

		} catch (SQLException e) {
			workspace.rollback();
			throw new SQLException(e);
		} finally {
			if (ps != null) {
				ps.close();
			}
		}
	}

	@Override
	public Foglalo updateFoglalo(Workspace workspace, Foglalo updateFoglalo)
			throws SQLException {
		PreparedStatement ps = null;
		Foglalo foglalo = null;

		workspace.startSession();

		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE FOGLALO SET WRITER = ?");
		if (updateFoglalo.getFoglaloNev() != null) {
			sql.append(", FNEV = ?");
		}
		if (updateFoglalo.getBelsoE() != null) {
			sql.append(", KULSO_BELSO = ?");
		}
		if (updateFoglalo.getKtsgHely() != null) {
			sql.append(", KTSG_TERMEKID = ?");
		}
		if (updateFoglalo.getKtsgViselo() != null) {
			sql.append(", KTSGVISELO = ?");
		}
		sql.append(" WHERE ID = ? AND ROWVERSION = ?");

		try {
			ps = workspace.getPreparedStatement(sql.toString());
			int i = 1;
			ps.setLong(i++, workspace.getAktualisOperator().getId());
			if (updateFoglalo.getFoglaloNev() != null) {
				ps.setString(i++, updateFoglalo.getFoglaloNev());
			}
			if (updateFoglalo.getBelsoE() != null) {
				ps.setBoolean(i++, updateFoglalo.getBelsoE());
			}
			if (updateFoglalo.getKtsgHely() != null) {
				ps.setString(i++, updateFoglalo.getKtsgHely());
			}
			if (updateFoglalo.getKtsgViselo() != null) {
				ps.setString(i++, updateFoglalo.getKtsgViselo());
			}
			ps.setLong(i++, updateFoglalo.getId());
			ps.setLong(i++, updateFoglalo.getRowVersion());
			int valtSorNum = ps.executeUpdate();
			if (valtSorNum == 1) {
				foglalo = this
						.loadFoglaloById(workspace, updateFoglalo.getId());
				workspace.commit();
			}
			return foglalo;

		} catch (SQLException e) {
			workspace.rollback();
			throw new SQLException(e);
		} finally {
			if (ps != null) {
				ps.close();
			}
		}
	}

	@Override
	public boolean deleteFoglalo(Workspace workspace, long id)
			throws SQLException {
		PreparedStatement ps = null;

		workspace.startSession();

		try {
			ps = workspace
					.getPreparedStatement("UPDATE FOGLALO SET WRITER = ?, AKTIV = 0 WHERE ID = ?");
			int i = 1;
			ps.setLong(i++, workspace.getAktualisOperator().getId());
			ps.setLong(i++, id);
			int valtSorNum = ps.executeUpdate();
			if (valtSorNum == 1) {
				workspace.commit();
				return true;
			} else {
				return false;
			}

		} catch (SQLException e) {
			workspace.rollback();
			throw new SQLException(e);
		} finally {
			if (ps != null) {
				ps.close();
			}
		}
	}

	@Override
	public boolean deleteFoglaloForTest(Workspace workspace, long id)
			throws SQLException {
		PreparedStatement ps = null;

		workspace.startSession();

		try {
			ps = workspace
					.getPreparedStatement("DELETE FROM FOGLALO WHERE ID = ?");
			int i = 1;
			ps.setLong(i++, id);
			int valtSorNum = ps.executeUpdate();
			if (valtSorNum == 1) {
				workspace.commit();
				return true;
			} else {
				return false;
			}

		} catch (SQLException e) {
			workspace.rollback();
			throw new SQLException(e);
		} finally {
			if (ps != null) {
				ps.close();
			}
		}
	}

	private Foglalo buildFoglalo(ResultSet rs) throws SQLException {
		int i = 1;
		Foglalo newFoglalo = new Foglalo();
		newFoglalo.setId(rs.getLong(i++));
		newFoglalo.setRowVersion(rs.getLong(i++));
		newFoglalo.setFoglaloNev(rs.getString(i++));
		newFoglalo.setBelsoE(rs.getBoolean(i++));
		newFoglalo.setKtsgHely(rs.getString(i++));
		newFoglalo.setKtsgViselo(rs.getString(i++));
		return newFoglalo;

	}

	private long getGeneratedKey(Workspace workspace) throws SQLException {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = workspace
					.getPreparedStatement("SELECT IDENT_CURRENT('FOGLALO')");
			rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getLong(1);
			} else
				return -1L;
		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null) {
				rs.close();
			}
		}
	}

}
